% cohort method to statistics on year data
clear all;
clc;
freqj=4;% 4 quarters
year_list=[1997:2010]';
year_list_text=num2str(year_list);
% quarter_list=[1:4]';
% quarter_list_text=num2str(quarter_list);
Rate_list=[0,1:0.5:5];
final_result=zeros(13,10,13);
% final_result=zeros(13,freqj,10,13); % 1-10 column is rating for 0-5, column 11 is default, column 12 is dwithdraw, column 13 is total

for i=1:13 % i=1:13 number of year
     %for iijj=1:freqj
%      for iijj=1:1
%         if iijj==freqj
%             for j=1:length(Rate_list) % number of diff rating 0-5
%                 
%                 db1_open = database('TP06_Y_RJ_fin_R5_G250_1000k_MR7','', ''); %open the connection to the database
%                 %db1_open = database('TP09_Q_final','', ''); %open the connection to the database Less than 100k 
%                 %sqlquery1 = strcat('SELECT qwry1.Year, qwry1.[Overall Risk Rating (Hist)] AS RRwS, qwry1.[Customer Number] FROM TP06_Y_RJ_fin_R5_G250_1000k_MR7 AS qwry1, input_vars AS [input], [LifeCyclePD04*a-Last_Fiscal_Month] AS qwry2 WHERE ((([qwry1].[year]+1)<[qwry2].[max_fiscal_year]) AND ((qwry1.Status) Is Null) AND ((qwry1.Year) Is Not Null) AND ((qwry1.Quarter)=',num2str(quarter_list_text(iijj,:)),')) OR ((([qwry1].[year]+1)=[qwry2].[max_fiscal_year]) AND ((qwry1.Status) Is Null) AND ((qwry1.Year) Is Not Null) AND ((qwry2.Max_Fiscal_Month)>=[input].[calender_year_start_month])) AND ((qwry1.Quarter)=',num2str(quarter_list_text(iijj,:)),')) GROUP BY qwry1.Year, qwry1.[Overall Risk Rating (Hist)], qwry1.[Customer Number],qwry1.[Quarter] HAVING (((qwry1.Year)=''',year_list_text(i,:),''') AND ((qwry1.[Overall Risk Rating (Hist)])=',num2str(Rate_list(j)),'))');
%                 sqlquery1 = strcat('SELECT qwry1.Year, qwry1.[Overall Risk Rating (Hist)] AS RRwS, qwry1.[Customer Number] FROM TP06_Y_RJ_fin_R5_G250_1000k_MR7 AS qwry1, input_vars AS [input], [LifeCyclePD04*a-Last_Fiscal_Month] AS qwry2 WHERE ((([qwry1].[year]+1)<[qwry2].[max_fiscal_year]) AND ((qwry1.Status) Is Null) AND ((qwry1.Year) Is Not Null) AND ((qwry1.Quarter)=',num2str(quarter_list_text(iijj,:)),')) OR ((([qwry1].[year]+1)=[qwry2].[max_fiscal_year]) AND ((qwry1.Status) Is Null) AND ((qwry1.Year) Is Not Null) AND ((qwry1.quarter)=',num2str(quarter_list_text(iijj,:)),') AND ((qwry2.Max_Fiscal_Month)>=[input].[calender_year_start_month])) GROUP BY qwry1.Year, qwry1.[Overall Risk Rating (Hist)], qwry1.[Customer Number],qwry1.[Quarter] HAVING (((qwry1.Year)=''',year_list_text(i,:),''') AND ((qwry1.[Overall Risk Rating (Hist)])=',num2str(Rate_list(j)),'))');
%                 
%                 curs = exec(db1_open, sqlquery1);
%                 curs = fetch(curs);
%                 db1_cell=curs.Data;
%                 close (db1_open);
%                 if strcmp(db1_cell,'No Data')
%                     disp('check database');
%                     i
%                     j
%                     iijj
%                     %final_result=zeros(j,:)=0;
%                 else
%                     custom_number_list=cell2mat(db1_cell(:,3));
%                     [total_num,col]=size(custom_number_list); % total_num is the number of companies from last year
%                     final_result(i,iijj,j,13)=total_num;
%                     clear curs sqlquery1 db1_cell custom_number_list db1_open;
%                     
%                     
%                     db1_open = database('TP06_Y_RJ_fin_R5_G250_1000k_MR7','', ''); %open the connection to the database
%                     sqlquery2 = strcat('SELECT [TP06_Y_RJ_fin_R5_G250_1000k_MR7].[Overall Risk Rating (Hist)] AS Expr1, Count([TP06_Y_RJ_fin_R5_G250_1000k_MR7].[Customer Number]) AS NumCN FROM TP06_Y_RJ_fin_R5_G250_1000k_MR7, (SELECT [TP06_Y_RJ_fin_R5_G250_1000k_MR7].[Customer Number] FROM TP06_Y_RJ_fin_R5_G250_1000k_MR7 WHERE (((TP06_Y_RJ_fin_R5_G250_1000k_MR7.Year)=''',year_list_text(i,:),''') And ((TP06_Y_RJ_fin_R5_G250_1000k_MR7.Quarter)=',num2str(quarter_list_text(iijj,:)),') And ((TP06_Y_RJ_fin_R5_G250_1000k_MR7.[Overall Risk Rating (Hist)])=',num2str(Rate_list(j)),') And ((TP06_Y_RJ_fin_R5_G250_1000k_MR7.Status) Is Null))) AS q1 WHERE (((TP06_Y_RJ_fin_R5_G250_1000k_MR7.Year)=''',year_list_text(i+1,:),''') And ((TP06_Y_RJ_fin_R5_G250_1000k_MR7.Quarter)=',num2str(quarter_list_text(1,:)),') And ((TP06_Y_RJ_fin_R5_G250_1000k_MR7.Status) Is Null)) And ((TP06_Y_RJ_fin_R5_G250_1000k_MR7.[Customer Number])=q1.[Customer Number]) GROUP BY [TP06_Y_RJ_fin_R5_G250_1000k_MR7].[Overall Risk Rating (Hist)]');
%                     curs = exec(db1_open, sqlquery2);
%                     curs = fetch(curs);
%                     db2_cell=curs.Data;
%                     close (db1_open);
%                     clear curs;
%                     stats_list=cell2mat(db2_cell);
%                     for ii=1:length(stats_list)
%                         switch stats_list(ii,1)
%                             case 0
%                                 final_result(i,iijj,j,1) = stats_list(ii,2);
%                             case 1
%                                 final_result(i,iijj,j,2) = stats_list(ii,2);
%                             case 1.5
%                                 final_result(i,iijj,j,3) = stats_list(ii,2);
%                             case 2
%                                 final_result(i,iijj,j,4) = stats_list(ii,2);
%                             case 2.5
%                                 final_result(i,iijj,j,5) = stats_list(ii,2);
%                             case 3
%                                 final_result(i,iijj,j,6) = stats_list(ii,2);
%                             case 3.5
%                                 final_result(i,iijj,j,7) = stats_list(ii,2);
%                             case 4
%                                 final_result(i,iijj,j,8) = stats_list(ii,2);
%                             case 4.5
%                                 final_result(i,iijj,j,9) = stats_list(ii,2);
%                             case 5
%                                 final_result(i,iijj,j,10) = stats_list(ii,2);
%                             otherwise
%                                 disp('WTF!!!')
% % %                                 overfivetemp = stats_list(ii,2);
%                         end
%                     end
%                     clear curs sqlquery2 db2_cell stats_list db1_open;
%                     
%                     db1_open = database('TP06_Y_RJ_fin_R5_G250_1000k_MR7','', ''); %open the connection to the database
%                     %sqlquery3 = strcat('SELECT Count([TP06_Y_RJ_fin_R5_G250_1000k_MR7].[Customer Number]) AS NumCN FROM TP06_Y_RJ_fin_R5_G250_1000k_MR7, (SELECT [TP06_Y_RJ_fin_R5_G250_1000k_MR7].[Customer Number] FROM TP06_Y_RJ_fin_R5_G250_1000k_MR7 WHERE (((TP06_Y_RJ_fin_R5_G250_1000k_MR7.Year)=''',year_list_text(i,:),''') And ((TP06_Y_RJ_fin_R5_G250_1000k_MR7.Quarter)=',num2str(quarter_list_text(iijj,:)),') And ((TP06_Y_RJ_fin_R5_G250_1000k_MR7.[Overall Risk Rating (Hist)])=',num2str(Rate_list(j)),') And ((TP06_Y_RJ_fin_R5_G250_1000k_MR7.Status) Is Null))) AS q1 WHERE (((TP06_Y_RJ_fin_R5_G250_1000k_MR7.Year)=''',year_list_text(i+1,:),''') And ((TP06_Y_RJ_fin_R5_G250_1000k_MR7.Quarter)=',num2str(quarter_list_text(1,:)),') And ((TP06_Y_RJ_fin_R5_G250_1000k_MR7.default)="default") And ((TP06_Y_RJ_fin_R5_G250_1000k_MR7.[Customer Number])=q1.[Customer Number])');
%                     sqlquery3 = strcat('SELECT Count(TP06_Y_RJ_fin_R5_G250_1000k_MR7.[Customer Number]) AS NumCN FROM TP06_Y_RJ_fin_R5_G250_1000k_MR7, (SELECT [TP06_Y_RJ_fin_R5_G250_1000k_MR7].[Customer Number] FROM TP06_Y_RJ_fin_R5_G250_1000k_MR7 WHERE (((TP06_Y_RJ_fin_R5_G250_1000k_MR7.Year)=''',year_list_text(i,:),''') And ((TP06_Y_RJ_fin_R5_G250_1000k_MR7.Quarter)=',num2str(quarter_list_text(iijj,:)),') And ((TP06_Y_RJ_fin_R5_G250_1000k_MR7.[Overall Risk Rating (Hist)])=',num2str(Rate_list(j)),') And ((TP06_Y_RJ_fin_R5_G250_1000k_MR7.Status) Is Null)))  AS q1 WHERE (((TP06_Y_RJ_fin_R5_G250_1000k_MR7.Year)=''',year_list_text(i+1,:),''') AND ((TP06_Y_RJ_fin_R5_G250_1000k_MR7.Quarter)=',num2str(quarter_list_text(1,:)),') AND ((TP06_Y_RJ_fin_R5_G250_1000k_MR7.Default)="Default") AND ((TP06_Y_RJ_fin_R5_G250_1000k_MR7.[Customer Number])=[q1].[Customer Number]))');
%                     curs = exec(db1_open, sqlquery3);
%                     curs = fetch(curs);
%                     db3_cell=curs.Data;
%                     close (db1_open);
%                     clear curs;
%                     if strcmp(db3_cell,'No Data')
%                         final_result(i,iijj,j,11) = 0;
%                     else
%                         default_list=cell2mat(db3_cell);
%                         final_result(i,iijj,j,11)= default_list(:,1);
%                         %default_list=db3_cell;
%                         %final_result(i,iijj,j,11) = default_list;
%                     end
%                     clear curs sqlquery3 db3_cell stats_list db1_open;
%                     
%                     final_result(i,iijj,j,12)=final_result(i,iijj,j,13)-sum(final_result(i,iijj,j,1:11));
%                     
% %                     clear withdrawtemp
%                 end
%             end
            
%         else
            for j=1:length(Rate_list); % number of diff rating 0-5
                
                db1_open = database('TP06_Y_RJ_fin_R5_G250_1000k_MR7','', ''); %open the connection to the database
                %sqlquery1 = strcat('SELECT qwry1.Year, qwry1.[Overall Risk Rating (Hist)] AS RRwS, qwry1.[Customer Number] FROM TP06_Y_RJ_fin_R5_G250_1000k_MR7 AS qwry1, input_vars AS [input], [LifeCyclePD04*a-Last_Fiscal_Month] AS qwry2 WHERE ((([qwry1].[year]+1)<[qwry2].[max_fiscal_year]) AND ((qwry1.Status) Is Null) AND ((qwry1.Year) Is Not Null) AND ((qwry1.Quarter)=',num2str(quarter_list_text(iijj,:)),')) OR ((([qwry1].[year]+1)=[qwry2].[max_fiscal_year]) AND ((qwry1.Status) Is Null) AND ((qwry1.Year) Is Not Null) AND ((qwry1.quarter)=',num2str(quarter_list_text(iijj,:)),') AND ((qwry2.Max_Fiscal_Month)>=[input].[calender_year_start_month])) GROUP BY qwry1.Year, qwry1.[Overall Risk Rating (Hist)], qwry1.[Customer Number], qwry1.[Quarter] HAVING (((qwry1.Year)=''',year_list_text(i,:),''') AND ((qwry1.[Overall Risk Rating (Hist)])=',num2str(Rate_list(j)),'))');
                sqlquery1 = strcat('SELECT qwry1.Year, qwry1.[Overall Risk Rating (Hist)] AS RRwS, qwry1.[Customer Number]FROM TP06_Y_RJ_fin_R5_G250_1000k_MR7 AS qwry1, input_vars AS [input], [LifeCyclePD04*a-Last_Fiscal_Month] AS qwry2 WHERE ((([qwry1].[year]+1)<[qwry2].[max_fiscal_year]) AND ((qwry1.Status) Is Null) AND ((qwry1.Year) Is Not Null)) OR ((([qwry1].[year]+1)=[qwry2].[max_fiscal_year]) AND ((qwry1.Status) Is Null) AND ((qwry1.Year) Is Not Null) AND ((qwry2.Max_Fiscal_Month)>=[input].[calender_year_start_month])) GROUP BY qwry1.Year, qwry1.[Overall Risk Rating (Hist)], qwry1.[Customer Number] HAVING (((qwry1.Year)=''',year_list_text(i,:),''') AND ((qwry1.[Overall Risk Rating (Hist)])=',num2str(Rate_list(j)),'))');
                curs = exec(db1_open, sqlquery1);
                curs = fetch(curs);
                db1_cell=curs.Data;
                close (db1_open);
                if strcmp(db1_cell,'No Data')
                    disp('check database');
                    i
                    j
                    %iijj
                    %final_result=zeros(j,:)=0;
                else
                    custom_number_list=cell2mat(db1_cell(:,3));
                    [total_num,col]=size(custom_number_list); % total_num is the number of companies from last year
                    final_result(i,j,13)=total_num;
                    %final_result(i,iijj,j,13)=total_num;
                    clear curs sqlquery1 db1_cell custom_number_list db1_open;
                    
                    
                    db1_open = database('TP06_Y_RJ_fin_R5_G250_1000k_MR7','', ''); %open the connection to the database
                    %sqlquery2 = strcat('SELECT [TP06_Y_RJ_fin_R5_G250_1000k_MR7].[Overall Risk Rating (Hist)] AS Expr1, Count([TP06_Y_RJ_fin_R5_G250_1000k_MR7].[Customer Number]) AS NumCN FROM TP06_Y_RJ_fin_R5_G250_1000k_MR7, (SELECT [TP06_Y_RJ_fin_R5_G250_1000k_MR7].[Customer Number] FROM TP06_Y_RJ_fin_R5_G250_1000k_MR7 WHERE (((TP06_Y_RJ_fin_R5_G250_1000k_MR7.Year)=''',year_list_text(i,:),''')And ((TP06_Y_RJ_fin_R5_G250_1000k_MR7.Quarter)=',num2str(quarter_list_text(iijj,:)),') And ((TP06_Y_RJ_fin_R5_G250_1000k_MR7.[Overall Risk Rating (Hist)])=',num2str(Rate_list(j)),') And ((TP06_Y_RJ_fin_R5_G250_1000k_MR7.Status) Is Null))) AS q1 WHERE (((TP06_Y_RJ_fin_R5_G250_1000k_MR7.Year)=''',year_list_text(i,:),''') And ((TP06_Y_RJ_fin_R5_G250_1000k_MR7.Quarter)=',num2str(quarter_list_text(iijj+1,:)),') And ((TP06_Y_RJ_fin_R5_G250_1000k_MR7.Status) Is Null)) And ((TP06_Y_RJ_fin_R5_G250_1000k_MR7.[Customer Number])=q1.[Customer Number]) GROUP BY [TP06_Y_RJ_fin_R5_G250_1000k_MR7].[Overall Risk Rating (Hist)]');
                    sqlquery2 = strcat('SELECT [TP06_Y_RJ_fin_R5_G250_1000k_MR7].[Overall Risk Rating (Hist)] AS Expr1, Count([TP06_Y_RJ_fin_R5_G250_1000k_MR7].[Customer Number]) AS NumCN FROM TP06_Y_RJ_fin_R5_G250_1000k_MR7, (SELECT [TP06_Y_RJ_fin_R5_G250_1000k_MR7].[Customer Number] FROM TP06_Y_RJ_fin_R5_G250_1000k_MR7 WHERE (((TP06_Y_RJ_fin_R5_G250_1000k_MR7.Year)=''',year_list_text(i,:),''') And ((TP06_Y_RJ_fin_R5_G250_1000k_MR7.[Overall Risk Rating (Hist)])=',num2str(Rate_list(j)),') And ((TP06_Y_RJ_fin_R5_G250_1000k_MR7.Status) Is Null))) AS q1 WHERE (((TP06_Y_RJ_fin_R5_G250_1000k_MR7.Year)=''',year_list_text(i+1,:),''') And ((TP06_Y_RJ_fin_R5_G250_1000k_MR7.Status) Is Null)) And ((TP06_Y_RJ_fin_R5_G250_1000k_MR7.[Customer Number])=q1.[Customer Number]) GROUP BY [TP06_Y_RJ_fin_R5_G250_1000k_MR7].[Overall Risk Rating (Hist)]');
                    curs = exec(db1_open, sqlquery2);
                    curs = fetch(curs);
                    db2_cell=curs.Data;
                    close (db1_open);
                    clear curs;
                    stats_list=cell2mat(db2_cell);
                    [mmx,nnx]=size(stats_list);
                    for ii=1:mmx
                        switch stats_list(ii,1)
                            case 0
                                final_result(i,j,1) = stats_list(ii,2);
                            case 1
                                final_result(i,j,2) = stats_list(ii,2);
                            case 1.5
                                final_result(i,j,3) = stats_list(ii,2);
                            case 2
                                final_result(i,j,4) = stats_list(ii,2);
                            case 2.5
                                final_result(i,j,5) = stats_list(ii,2);
                            case 3
                                final_result(i,j,6) = stats_list(ii,2);
                            case 3.5
                                final_result(i,j,7) = stats_list(ii,2);
                            case 4
                                final_result(i,j,8) = stats_list(ii,2);
                            case 4.5
                                final_result(i,j,9) = stats_list(ii,2);
                            case 5
                                final_result(i,j,10) = stats_list(ii,2);                            
                            otherwise
                                disp('WTF!!!')
                        end
                    end
                    clear curs sqlquery2 db2_cell stats_list db1_open;
                    
                    db1_open = database('TP06_Y_RJ_fin_R5_G250_1000k_MR7','', ''); %open the connection to the database
                    %sqlquery3 = strcat('SELECT Count(TP06_Y_RJ_fin_R5_G250_1000k_MR7.[Customer Number]) AS NumCN FROM TP06_Y_RJ_fin_R5_G250_1000k_MR7, (SELECT [TP06_Y_RJ_fin_R5_G250_1000k_MR7].[Customer Number] FROM TP06_Y_RJ_fin_R5_G250_1000k_MR7 WHERE (((TP06_Y_RJ_fin_R5_G250_1000k_MR7.Year)=''',year_list_text(i,:),''') And ((TP06_Y_RJ_fin_R5_G250_1000k_MR7.Quarter)=',num2str(quarter_list_text(iijj,:)),') And ((TP06_Y_RJ_fin_R5_G250_1000k_MR7.[Overall Risk Rating (Hist)])=',num2str(Rate_list(j)),') And ((TP06_Y_RJ_fin_R5_G250_1000k_MR7.Status) Is Null)))  AS q1 WHERE (((TP06_Y_RJ_fin_R5_G250_1000k_MR7.Year)=''',year_list_text(i,:),''') AND ((TP06_Y_RJ_fin_R5_G250_1000k_MR7.Quarter)=',num2str(quarter_list_text(iijj+1,:)),') AND ((TP06_Y_RJ_fin_R5_G250_1000k_MR7.Default)="Default") AND ((TP06_Y_RJ_fin_R5_G250_1000k_MR7.[Customer Number])=[q1].[Customer Number]))');
                    sqlquery3 = strcat('SELECT Count(TP06_Y_RJ_fin_R5_G250_1000k_MR7.[Customer Number]) AS NumCN FROM TP06_Y_RJ_fin_R5_G250_1000k_MR7, (SELECT [TP06_Y_RJ_fin_R5_G250_1000k_MR7].[Customer Number] FROM TP06_Y_RJ_fin_R5_G250_1000k_MR7 WHERE (((TP06_Y_RJ_fin_R5_G250_1000k_MR7.Year)=''',year_list_text(i,:),''') And ((TP06_Y_RJ_fin_R5_G250_1000k_MR7.[Overall Risk Rating (Hist)])=',num2str(Rate_list(j)),') And ((TP06_Y_RJ_fin_R5_G250_1000k_MR7.Status) Is Null)))  AS q1 WHERE (((TP06_Y_RJ_fin_R5_G250_1000k_MR7.Year)=''',year_list_text(i,:),''') AND ((TP06_Y_RJ_fin_R5_G250_1000k_MR7.Default)="Default") AND ((TP06_Y_RJ_fin_R5_G250_1000k_MR7.[Customer Number])=[q1].[Customer Number]))');
                    curs = exec(db1_open, sqlquery3);
                    curs = fetch(curs);
                    db3_cell=curs.Data;
                    close (db1_open);
                    clear curs;
                    if strcmp(db3_cell,'No Data')
                        final_result(i,j,11) = 0;
                    else
                        default_list=cell2mat(db3_cell);
                        final_result(i,j,11)= default_list(:,1);
                        %final_result(i,iijj,j,11) = default_list(ii,2);
                    end
                    clear curs sqlquery3 db3_cell stats_list db1_open;
                    
                    final_result(i,j,12)=final_result(i,j,13)-sum(final_result(i,j,1:11));
                end
            end
%         end
%     end
end